package cn.butcher21.dao;

import cn.butcher21.utils.DBUtil;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * butcher create in 2021/05/15/10:37
 */
public class ChartDao {
    public List<Map<String, Object>> getChartData(String start, String end) throws SQLException, IOException, ClassNotFoundException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        DBUtil dbUtil = new DBUtil();
        String sql = "SELECT\n" +
                "\tSUBSTR( s_id, 3, 8 ) date,\n" +
                "\tSUM( g.g_cost ) cost,\n" +
                "\tSUM( s.s_count ) count,\n" +
                "\tSUM( ( g.g_price - g.g_cost ) * s.s_count ) profit \n" +
                "FROM\n" +
                "\tt_goods g,\n" +
                "\tt_sale s \n" +
                "WHERE\n" +
                "\tg.g_id = s.g_id \n" +
                "\tAND (\n" +
                "\t\tSUBSTR( s_id, 3, 8 ) BETWEEN ? \n" +
                "\t\tAND ? )\n" +
                "GROUP BY\n" +
                "\tSUBSTR( s_id, 3, 8 ) ORDER BY date";
        PreparedStatement ps = dbUtil.getPs(sql);
        ps.setString(1, start);
        ps.setString(2, end);
        ResultSet rs = dbUtil.getRs(ps.executeQuery());
        while (rs.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("date", rs.getString("date"));
            map.put("cost", rs.getInt("count"));
            map.put("count", rs.getDouble("cost"));
            map.put("profit", rs.getDouble("profit"));
            list.add(map);
        }
        dbUtil.close();
        return list;
    }

    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        ChartDao chartDao = new ChartDao();
        List<Map<String, Object>> chartData = chartDao.getChartData("20210507", "20210514");
        System.out.println(chartData);
    }
}
